119f6b0bd8b27f55670e8d31f84f908c7d78895b,engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java,Upgrade218to22,upgradePortForwardingRules,#Connection#,1204

Before Change



                    // update port_forwarding_rules table
                    s_logger.trace("Updating port_forwarding_rules table...");
                    pstmt = conn.prepareStatement("SELECT instance_id FROM nics where network_id=? AND ip4_address=?");
                    pstmt.setLong(1, networkId);
                    pstmt.setString(2, privateIp);
                    rs = pstmt.executeQuery();

                    if (!rs.next()) {
                        // the vm might be expunged already...so just give the warning
                        s_logger.warn("Unable to find vmId for private ip address " + privateIp + " for account id=" + accountId + "; assume that the vm is expunged");
                        // throw new CloudRuntimeException("Unable to find vmId for private ip address " + privateIp +
                        // " for account id=" + accountId);
                    } else {
                        long instanceId = rs.getLong(1);
                        s_logger.debug("Instance id is " + instanceId);
                        // update firewall_rules table
                        s_logger.trace("Updating firewall_rules table as a part of PF rules upgrade...");
                        pstmt =
                            conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'PortForwarding',       ?,      ?,      ?,      ?,       0,     now())");
                        pstmt.setLong(1, id);
                        pstmt.setInt(2, ipAddressId);
                        pstmt.setInt(3, Integer.parseInt(sourcePort.trim()));
                        pstmt.setInt(4, Integer.parseInt(sourcePort.trim()));
                        pstmt.setString(5, protocol);
                        pstmt.setLong(6, accountId);
                        pstmt.setLong(7, domainId);
                        pstmt.setLong(8, networkId);
                        pstmt.setString(9, UUID.randomUUID().toString());
                        pstmt.executeUpdate();
                        pstmt.close();
                        s_logger.trace("firewall_rules table is updated as a part of PF rules upgrade");

                        rs.close();
                        pstmt.close();

                        String privatePort = (String)rule[4];
                        pstmt = conn.prepareStatement("INSERT INTO port_forwarding_rules VALUES (?,    ?,      ?,      ?,       ?)");
                        pstmt.setLong(1, id);
                        pstmt.setLong(2, instanceId);
                        pstmt.setString(3, privateIp);
                        pstmt.setInt(4, Integer.parseInt(privatePort.trim()));
                        pstmt.setInt(5, Integer.parseInt(privatePort.trim()));
                        pstmt.executeUpdate();
                        pstmt.close();
                        s_logger.trace("port_forwarding_rules table is updated");

                    }

After Change



                            // update port_forwarding_rules table
                            s_logger.trace("Updating port_forwarding_rules table...");
                            try (PreparedStatement selectInstanceId = conn.prepareStatement("SELECT instance_id FROM nics where network_id=? AND ip4_address=?");) {
                                selectInstanceId.setLong(1, networkId);
                                selectInstanceId.setString(2, privateIp);
                                try (ResultSet selectedInstanceId = selectInstanceId.executeQuery();) {

                                    if (!selectedInstanceId.next()) {
                                        // the vm might be expunged already...so just give the warning
                                        s_logger.warn("Unable to find vmId for private ip address " + privateIp + " for account id=" + accountId + "; assume that the vm is expunged");
                                        // throw new CloudRuntimeException("Unable to find vmId for private ip address " + privateIp +
                                        // " for account id=" + accountId);
                                    } else {
                                        long instanceId = selectedInstanceId.getLong(1);
                                        s_logger.debug("Instance id is " + instanceId);
                                        // update firewall_rules table
                                        s_logger.trace("Updating firewall_rules table as a part of PF rules upgrade...");
                                        try (
                                                PreparedStatement insertFirewallRules =
                                                conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'PortForwarding',       ?,      ?,      ?,      ?,       0,     now())");
                                            ) {
                                            insertFirewallRules.setLong(1, id);
                                            insertFirewallRules.setInt(2, ipAddressId);
                                            insertFirewallRules.setInt(3, Integer.parseInt(sourcePort.trim()));
                                            insertFirewallRules.setInt(4, Integer.parseInt(sourcePort.trim()));
                                            insertFirewallRules.setString(5, protocol);
                                            insertFirewallRules.setLong(6, accountId);
                                            insertFirewallRules.setLong(7, domainId);
                                            insertFirewallRules.setLong(8, networkId);
                                            insertFirewallRules.setString(9, UUID.randomUUID().toString());
                                            insertFirewallRules.executeUpdate();
                                            s_logger.trace("firewall_rules table is updated as a part of PF rules upgrade");
                                        }
                                        String privatePort = (String)rule[4];
                                        try (PreparedStatement insertPortForwardingRules = conn.prepareStatement("INSERT INTO port_forwarding_rules VALUES (?,    ?,      ?,      ?,       ?)");) {
                                            insertPortForwardingRules.setLong(1, id);
                                            insertPortForwardingRules.setLong(2, instanceId);
                                            insertPortForwardingRules.setString(3, privateIp);
                                            insertPortForwardingRules.setInt(4, Integer.parseInt(privatePort.trim()));
                                            insertPortForwardingRules.setInt(5, Integer.parseInt(privatePort.trim()));
                                            insertPortForwardingRules.executeUpdate();
                                        }
                                        s_logger.trace("port_forwarding_rules table is updated");
                                    }